from flask import Flask, render_template, request,jsonify,redirect,url_for
import pymysql
import os
import  datetime
app = Flask(__name__)


#页面，登录界面
@app.route('/page/login')
def login_page():
    return  render_template('/pages/login.html')
#页面，注册界面
@app.route('/page/register')
def register_page():
    return  render_template('/pages/register.html')

#接口，登录按钮，登录成功返回主页面index，登陆失败回到本页面并提示错误信息
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method=='POST':
        username=request.form.get('username')
        password=request.form.get('password')
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                               db='restaurant')  # 链接mysql
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        order = "select username,password from user where username=%s;"
        cursor.execute(order, [username, ])
        data = cursor.fetchone()
        if(username==data['username'] and password==data['password']):
            cursor.close()
            conn.close()
            return redirect(url_for('index',username=username))
        else:
            return render_template('/pages/login.html',info='请检查用户名或密码是否正确！')
    else:
        return render_template('/pages/login.html')



#接口，注册按钮，注册成功返回登录界面，失败提示信息
@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method=='POST':
        username = request.form.get('username')
        password = request.form.get('password')
        print(username,password)
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                               db='restaurant')  # 链接mysql
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            order="insert into user(username,password) values (%s,%s);"
            cursor.execute(order,[username,password])
            conn.commit()
            cursor.close()
            conn.close()
            return render_template('pages/login.html', )
        except pymysql.err.IntegrityError:
            return  render_template('/pages/register.html',info='该用户名已经注册')

#接口，index界面的退出登录按钮，点击后回到登录界面
@app.route('/exit')
def exit():
    return redirect(url_for('login'))









#页面，用户登录成功后跳转到页面主界面
@app.route('/index/<username>', methods=['GET', 'POST'])
def index(username):
    #print(username)
    return render_template('/pages/index.html',username=username)







#页面，首页
@app.route('/home', methods=['GET', 'POST'])
def home():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = "select * from ord order by DELTIME desc limit 0, 50;"
    cursor.execute(order)
    now=datetime.datetime.now()
    year=now.year

    month=now.month
    month_start = str(year) + '-0' + str(month)+'-01'
    t=now.strftime('%Y-%m-%d')
    day_start=t+' 00:00:00'
    day_end=t+' 23:59:59'
    ord_list = cursor.fetchall()
    order1="select sum(price) from ord where deltime between %s and %s;"
    order2="select count(*) from ord where deltime between %s and %s;"
    order3 = "select count(*) from ord where deltime between %s and %s;"
    cursor.execute(order1,[month_start,t])
    sum=cursor.fetchone()
    cursor.execute(order2,[month_start,t])
    num = cursor.fetchone()
    cursor.execute(order3, [day_start, day_end])
    day_num = cursor.fetchone()
    #print(sum['sum(price)'],num['count(*)'])
    cursor.close()
    conn.close()
    return render_template('/pages/home.html',ord_list=ord_list,sum=sum,num=num,day_num=day_num)









#函数，返回每一类的菜品列表
def food_select(num):

    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order=''
    if num==1:
        order="select * from dish where CATEGORY like'%荤菜%' and visible=1;"
    elif num==2:
        print('指令2')
        order = "select * from dish where CATEGORY='素菜' and visible=1;"
    elif num==3:
        order = "select * from dish where CATEGORY='主食' and visible=1;"
    elif num==4:
        order = "select * from dish where CATEGORY='饮品' and visible=1;"
    elif num==5:
        order = "select * from dish where CATEGORY='甜品' and visible=1;"
    elif num==6:
        order = "select * from dish where CATEGORY='汤类' and visible=1;"
    elif num==7:
        order = "select * from dish where CATEGORY='凉菜' and visible=1;"

    cursor.execute(order)
    data_list = cursor.fetchall()
    #print(data_list)
    cursor.close()
    conn.close()
    return data_list

#页面，菜品管理》菜品分类
@app.route('/menu_select', methods=['GET', 'POST'])
def menu_select():
    # data_list=select()
    return render_template('/pages/menu.html')

#页面，菜品管理》菜品分类》饮品
@app.route('/drink_select', methods=['GET', 'POST'])
def drink_select():
    data_list=food_select(4)
    return render_template('/pages/food_class/dish.html',data=data_list)
#页面，菜品管理》菜品分类》主食
@app.route('/staple_select', methods=['GET', 'POST'])
def staple_select():
    data_list=food_select(3)
    return render_template('/pages/food_class/dish.html',data=data_list)
#页面，菜品管理》菜品分类》荤菜
@app.route('/meat_select', methods=['GET', 'POST'])
def meat_select():
    data_list=food_select(1)
    return render_template('/pages/food_class/dish.html',data=data_list)
#页面，菜品管理》菜品分类》素菜
@app.route('/vege_select', methods=['GET', 'POST'])
def vege_select():
    data_list=food_select(2)
    return render_template('/pages/food_class/dish.html',data=data_list)

#页面，菜品管理》菜品分类》甜品
@app.route('/sweet_select', methods=['GET', 'POST'])
def sweet_select():
    data_list=food_select(5)
    return render_template('/pages/food_class/dish.html',data=data_list)
#页面，菜品管理》菜品分类》汤类
@app.route('/soup_select', methods=['GET', 'POST'])
def soup_select():
    data_list=food_select(6)
    return render_template('/pages/food_class/dish.html',data=data_list)
#页面，菜品管理》菜品分类》凉菜
@app.route('/cool_select', methods=['GET', 'POST'])
def cool_select():
    data_list=food_select(7)
    return render_template('/pages/food_class/dish.html',data=data_list)

#页面，点击菜品》菜品信息
@app.route('/dish_info', methods=['GET', 'POST'])
def dish_info_select():
    id = request.args.get('id')
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = 'select * from dish where DNO=%s'
    cursor.execute(order,[id,])
    data = cursor.fetchone()
    order2="select count(*) num  from ord where dname=(select dname from dish where dno=%s)"
    cursor.execute(order2, [id, ])
    num = cursor.fetchone()
    #print(data)
    print(num)
    cursor.close()
    conn.close()
    return render_template('/pages/food_info/food_info.html',data=data,num=num['num'])


#接口，点击菜品》菜品信息》编辑按钮，点击后回到菜品信息界面
@app.route('/dish_edit', methods=['GET', 'POST'])
def dish_edit():
    id = request.args.get('id')
    name = request.args.get('name')
    cost = request.args.get('cost')
    price = request.args.get('price')
    category = request.args.get('category')
    info = request.args.get('info')
    print(id,name,cost,price,category,info)
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = 'update dish set dname=%s,price=%s,cost=%s,category=%s,intro=%s where dno=%s;'
    cursor.execute(order, [name,price,cost,category,info,id ])
    conn.commit()

    order = 'select * from dish where DNO=%s;'
    cursor.execute(order, [id, ])
    data = cursor.fetchone()
    print(data)
    cursor.close()
    conn.close()
    return render_template('/pages/food_info/food_info.html', data=data)

#接口，点击菜品》菜品信息》删除按钮，点击后回到菜品分类界面
@app.route('/dish_delete', methods=['GET', 'POST'])
def dish_delete():
    id = request.args.get('id')
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = "update dish set visible=0 where dno=%s;"
    cursor.execute(order, [id, ])
    conn.commit()
    cursor.close()
    conn.close()
    data_list = food_select(1)
    return render_template('/pages/food_class/dish.html', data=data_list)

#接口，菜品管理》增加新菜》保存添加按钮，点击后回到增加新菜界面
@app.route('/menu_add', methods=['GET', 'POST'])
def menu_add():
    file=request.args.get('file')
    print(file)
    return render_template('/pages/food_info/add_food.html')

#接口，菜品管理》增加新菜》保存添加按钮，点击后上传图片到服务器
@app.route('/img_upload', methods=['POST'])
def get_img():
    from datetime import datetime
    basedir = os.path.abspath(os.path.dirname(__file__))
    img = request.files.get('myfile')  # 拿到的也是一个字典 利用get取value
    name=request.form.get('name')
    category=request.form.get('category')
    price=request.form.get('price')
    cost=request.form.get('cost')
    info=request.form.get('info')
    path=basedir+'/static/img/menu/'
    time=datetime.now().strftime("%Y%m%d_%H%M%S")
    imgName=img.filename
    file_path = path + time+imgName
    img.save(file_path)
    url = '/static/img/menu/' +time+ imgName
    print(name,category,cost,price,info,url)
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = "insert into dish(DNAME,price,cost,CATEGORY,intro,pic) values(%s,%s,%s,%s,%s,%s);"
    cursor.execute(order, [name,price,cost,category,info,url ])
    conn.commit()

    cursor.close()
    conn.close()
    return render_template('/pages/menu.html')

#页面，菜品管理》数据分析
@app.route('/dish', methods=['GET', 'POST'])
def dish():
    data_list=select_all_dish_ord()#历史总榜单

    # data_list=[x for x in reversed(data_list)]
    #print(data_list)
    return render_template('/pages/dish.html',data_list=data_list)


#函数，返回近三个月菜品的销售情况
def selectDishSales():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = "SELECT temp2.DNAME,COUNT(*) as N FROM (SELECT ord.DNAME,ord.DELTIME FROM ord,(SELECT MAX(ord.DELTIME) as M FROM ord) as temp WHERE ord.DELTIME BETWEEN DATE_ADD(temp.M,INTERVAL -3 MONTH) AND temp.M) AS temp2 GROUP BY temp2.DNAME ORDER BY N;"
    cursor.execute(order)
    data_list = cursor.fetchall()
   # print(data_list)
    cursor.close()
    conn.close()
    return data_list


# 接口，返回近三月各菜品销量数据
@app.route('/dishSales', methods=['GET', 'POST'])
def dishSales():
    list = selectDishSales()
    return jsonify(list)  # 将list包装成json（字典）发送回去

#函数，返回每个菜的销售情况，用于制作榜单
def selectDishSalesSum():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = "SELECT ord.DNAME as name,COUNT(*) as value FROM ord GROUP BY ord.DNAME;"
    cursor.execute(order)
    data_list = cursor.fetchall()
    # print(data_list)
    cursor.close()
    conn.close()
    return data_list


# 接口，近各菜品历史累计销量数据
@app.route('/dishSalesSum', methods=['GET', 'POST'])
def dishSalesSum():
    list = selectDishSalesSum()
    return jsonify(list)  # 将list包装成json（字典）发送回去

#函数，返回菜品榜单信息
def select_all_dish_ord():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    order="select dish.dname as name,dish.price as price , dish.pic as pic ,count(*) as num  from dish ,ord where ord.dname=dish.dname and dish.visible=1 group by dish.dno order by num desc; "
    cursor.execute(order)
    data_list = cursor.fetchall()
    # print(data_list)
    cursor.close()
    conn.close()
    return data_list











#页面，顾客信息界面
@app.route('/customer_select', methods=['GET', 'POST'])
def customer_select():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = "select customer.cno no,customer.cname name,customer.clocation location,customer.cphone phone,count(*) num from customer left join ord on customer.cname=ord.cname group by customer.cno,customer.cname;"
    cursor.execute(order)
    data_list = cursor.fetchall()
    #print(data_list)
    #print(data_list)
    cursor.close()
    conn.close()
    return render_template('/pages/customer/customer_info.html',data_list=data_list)












#函数，返回员工的姓名，工资
def selectEmployee():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = "select ENAME,WAGES from employee;"
    cursor.execute(order)
    data_list = cursor.fetchall()
    # print(data_list)
    cursor.close()
    conn.close()
    return data_list

# 接口，返回员工的姓名工资
@app.route('/employee', methods=['GET', 'POST'])
def employee():
    list = selectEmployee()
    return jsonify(list)  # 将list包装成json（字典）发送回去


#函数，返回员工信息
def select():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = "select * from employee;"
    cursor.execute(order)
    data_list = cursor.fetchall()
    #print(data_list)
    cursor.close()
    conn.close()
    return data_list

#页面，员工管理
@app.route('/employee_select', methods=['GET', 'POST'])
def employee_select():
    data_list = select()
    return render_template('/pages/employee.html', data=data_list)

#接口，员工管理》删除按钮，点击之后回到员工管理界面
@app.route('/employee_delete', methods=['GET', 'POST'])
def employee_delete():
    print(request.args)
    id = request.args.get('id')
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = "delete from employee where eno=%s;"
    cursor.execute(order, [id, ])
    conn.commit()
    cursor.close()
    conn.close()
    data_list = select()
    return render_template('/pages/employee.html', data=data_list)

#接口，员工管理》增加按钮，点击之后回到员工管理界面
@app.route('/employee_insert', methods=['GET', 'POST'])
def employee_insert():
    name = request.args.get('name')
    sex = request.args.get('sex')

    phone = request.args.get('phone')
    wages = request.args.get('wages')
    #print(name, sex,phone,wages)
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = "insert into employee(ename,esex,ephone,wages) values(%s,%s,%s,%s)"
    cursor.execute(order, [name, sex, phone,wages])
    conn.commit()
    cursor.close()
    conn.close()
    data_list = select()
    return render_template('/pages/employee.html', data=data_list)

#接口，员工管理》编辑按钮，点击之后回到员工管理界面
@app.route('/employee_edit', methods=['GET', 'POST'])
def employee_edit():
    id=int(request.args.get('id'))
    name = request.args.get('name')
    sex = request.args.get('sex')

    phone = request.args.get('phone')
    wages=request.args.get('wages')
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = "update employee set ename=%s,esex=%s,ephone=%s,wages=%s where eno=%s;"
    cursor.execute(order, [name, sex, phone,wages, id])
    conn.commit()

    cursor.close()
    conn.close()
    data_list = select()
    return render_template('/pages/employee.html', data=data_list)














#界面，订单记录界面
@app.route('/order_select', methods=['GET', 'POST'])
def order_select():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = "select * from ord;"
    cursor.execute(order)
    data_list = cursor.fetchall()
    #print(data_list)
    cursor.close()
    conn.close()
    return render_template('/pages/order/order_info.html',data=data_list)












#页面，营业分析界面
@app.route('/business', methods=['GET', 'POST'])
def business():
    return render_template('/pages/business.html')

#函数，返回订单的统计情况
def selectOrdN():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = "SELECT YEAR(ord.DELTIME) as 'year',MONTH(ord.DELTIME) as 'month',COUNT(*) as 'ordN' FROM ord GROUP BY YEAR(ord.DELTIME),MONTH(ord.DELTIME) ORDER BY YEAR(ord.DELTIME),MONTH(ord.DELTIME);"
    cursor.execute(order)
    data_list = cursor.fetchall()
    order = "SELECT YEAR(ord.DELTIME) as 'year',MONTH(ord.DELTIME) as 'month',SUM(ord.PRICE) as 'turnover' FROM ord GROUP BY YEAR(ord.DELTIME),MONTH(ord.DELTIME) ORDER BY YEAR(ord.DELTIME),MONTH(ord.DELTIME);"
    cursor.execute(order)
    data_list = data_list.__add__(cursor.fetchall())
    # print(data_list)
    cursor.close()
    conn.close()
    return data_list

#接口，返回订单统计情况
@app.route('/ordN', methods=['GET', 'POST'])
def ordN():
    list = selectOrdN()
    ordNList, turnoverList = [], []
    for elem in list:
        if elem.__contains__('ordN'):
            ordNList.append(elem)
        else:
            turnoverList.append(elem)
    return jsonify([ordNList, turnoverList])  # 将list包装成json（字典）发送回去

#函数，返回客户统计情况
def selectCusOrdN():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = "SELECT temp.ordN,COUNT(*) as N FROM (SELECT CNAME,COUNT(*) as 'ordN' FROM ord GROUP BY ord.CNAME)temp GROUP BY temp.ordN ORDER BY temp.ordN;"
    cursor.execute(order)
    data_list = cursor.fetchall()
    # print(data_list)
    cursor.close()
    conn.close()
    return data_list


# 接口，返回客户统计情况信息
@app.route('/cusOrdN', methods=['GET', 'POST'])
def cusOrdN():
    list = selectCusOrdN()
    return jsonify(list)  # 将list包装成json（字典）发送回去

#函数，返回用户分级情况
def selectDegree():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = '''SELECT t.degree as 'name',COUNT(*) as 'value' FROM (SELECT (CASE 
                WHEN  temp.ordN <= 3 THEN
                    '一级'
                WHEN temp.ordN <= 6 THEN
                    '二级'
                ELSE
                    '三级'
            END) as 'degree'
            FROM (SELECT CNAME,COUNT(*) as 'ordN' FROM ord GROUP BY ord.CNAME) as temp) as t GROUP BY t.degree ORDER BY t.degree;
    '''
    cursor.execute(order)
    data_list = cursor.fetchall()
    # print(data_list)
    cursor.close()
    conn.close()
    return data_list


# 接口，返回用户分级情况
@app.route('/degree', methods=['GET', 'POST'])
def degree():
    list = selectDegree()
    return jsonify(list)  # 将list包装成json（字典）发送回去








if __name__ == '__main__':
    #app.run(debug=True, host='127.0.0.1', port='8001')
    app.run(debug=True, host='192.168.43.181', port='8001')
